<?php

/**
 *  學生學期記錄
 * @author cik
 *
 */
class Default_Model_Upgrade_StudSeme extends Default_Model_Upgrade_Core
{
    protected $_table = 'stud_seme';

    protected $_targetTable = array('student_semester');

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            $hasData = 0 ;
            // 先判斷是否有資料
            $query = "SELECT COUNT(*)  AS cc FROM student_semester";
            $res = $this->_newdbPdo->query($query)->fetch();
            $hasData = $res['cc'];

            $query = "ALTER TABLE `student_semester`
  DROP FOREIGN KEY `student_semester_ibfk_1`  ";
            $this->_newdbPdo->exec($query);
            $query = "ALTER TABLE `student_semester`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);


            $query = "SELECT * FROM stud_seme";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();

                $arr['year'] = (int)substr($row['seme_year_seme'],0,-1);
                $arr['semester'] = (int)substr($row['seme_year_seme'],-1);
                $arr['grade'] = (int)substr($row['seme_class'],0,1);
                $arr['class_sn'] = (int)substr($row['seme_class'],1);
                $arr['number'] = $row['seme_num'];
                $arr['student_sn'] = $row['student_sn'];
                $arr['student_id'] = $row['stud_id'];
                $arr['class'] = $this->iconv($row['seme_class_name']);
                if ($hasData) {
                    $find = $this->_newdbPdo->query("SELECT student_sn FROM student_semester
				  WHERE student_sn ='{$arr['student_sn']}'
				  AND year ='{$arr['year']}'
				  AND semester ='{$arr['semester']}'
				  AND grade ='{$arr['grade']}'
				  ");
                    if ($find->rowCount() > 0)
                    continue;
                }
                // 假別
                $query = "SELECT * FROM stud_seme_abs WHERE seme_year_seme='{$row['seme_year_seme']}'
					AND stud_id='{$row['stud_id']}' ";
                $res2 = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
                foreach($res2 as $row2){
                    switch($row2['abs_kind']){
                        case '1' :
                            $kind = 'personal_leave';
                            break;
                        case '2' :
                            $kind = 'sick_leave';
                            break;
                        case '3' :
                            $kind = 'truancy';
                            break;
                        case '4' :
                            $kind = 'assembly';
                            break;
                        case '5' :
                            $kind = 'official_leave';
                            break;
                        case '6' :
                            $kind = 'other_leave';
                            break;
                    }
                    $arr[$kind] = $row2['abs_days'];
                }

                $this->fromArray('student_semester',$arr);
            }

            $query = "ALTER TABLE `student_semester`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_semester`
  ADD CONSTRAINT `student_semester_ibfk_1` FOREIGN KEY (`student_sn`) REFERENCES `student` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;
            ";
            $this->_newdbPdo->exec($query);

            $log['memo'] = '學生學期資料';
            $log['upgrade_time']	= date("Y-m-d h:i:s");

            $this->finishUpgrade($log);

        }
    }
}